package com.teng.exceltest.util;


import com.spire.xls.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.*;

public class Excel2ImageUtil {
    /**
     * 将excel转换为图片并保存
     * @param args
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static void main(String[] args) throws IOException, InvalidFormatException {
        String path = "C:\\Users\\teng\\Desktop\\123.xlsx";
        ByteArrayOutputStream outputStream = putFile2Stream(path);
        ByteArrayInputStream inputStreamSource = new ByteArrayInputStream(outputStream.toByteArray());

//        new InputStream(outputStream);


        //Create a workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
//        workbook.loadFromFile(path);
        workbook.loadFromStream(inputStreamSource);

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Save the sheet to image
        sheet.saveToImage("image.png");
//        BufferedImage bufferedImage = sheet.toImage(firstRowNum, firstCellNum, lastRowNum, lastCellNum);
    }

    /**
     * 将excel转换为图片流并返回
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static BufferedImage getImage() throws IOException, InvalidFormatException {
        String path = "static/123.xlsx";

        ByteArrayOutputStream outputStream = putFile2Stream(path);
        ByteArrayInputStream inputStreamSource = new ByteArrayInputStream(outputStream.toByteArray());

        //Create a workbook instance
        Workbook workbook = new Workbook();
        workbook.loadFromStream(inputStreamSource);
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Save the sheet to image
        sheet.saveToImage("image.png");
        BufferedImage bufferedImage = sheet.toImage(firstRowNum, firstCellNum, lastRowNum, lastCellNum);
        System.out.println(bufferedImage.getType());
        //图片颜色校正
        BufferedImage bufferedImage1 = new BufferedImage(bufferedImage.getWidth(),bufferedImage.getHeight(),BufferedImage.TYPE_INT_RGB);
        Graphics2D graphics = bufferedImage1.createGraphics();
        graphics.drawImage(bufferedImage,0,0,bufferedImage.getWidth(),bufferedImage.getHeight(),null);
        graphics.dispose();
        return bufferedImage1;
    }



    private static int firstRowNum;
    private static int lastRowNum;
    private static short firstCellNum=Short.MAX_VALUE;
    private static short lastCellNum;

    public static ByteArrayOutputStream  putFile2Stream(String path) throws IOException, InvalidFormatException {
        ClassPathResource classPathResource = new ClassPathResource(path);
//        FileInputStream fileInputStream = new FileInputStream(new File(path));
        XSSFWorkbook sheets = new XSSFWorkbook(classPathResource.getInputStream());
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        firstRowNum = sheetAt.getFirstRowNum();
        lastRowNum = sheetAt.getLastRowNum();
        for (int i = firstRowNum; i < lastRowNum; i++) {
            XSSFRow row = sheetAt.getRow(i);
            if(row==null){
                continue;
            }
            short firstCellNum = row.getFirstCellNum();
            Excel2ImageUtil.firstCellNum = Excel2ImageUtil.firstCellNum < firstCellNum ? Excel2ImageUtil.firstCellNum : firstCellNum;
            short lastCellNum = row.getLastCellNum();
            Excel2ImageUtil.lastCellNum = Excel2ImageUtil.lastCellNum > firstCellNum ? Excel2ImageUtil.lastCellNum : lastCellNum;
            for (short j = firstCellNum; j < lastCellNum; j++) {
                XSSFCell cell = row.getCell(j);
                String rawValue = cell.getRawValue();
                CellType cellTypeEnum = cell.getCellTypeEnum();
                if (CellType.STRING!=cellTypeEnum){
                    continue;
                }
                String stringCellValue = cell.getStringCellValue();
                if (stringCellValue != null &&stringCellValue.matches("\\{.*\\}")) {// stringCellValue.matches("\\{.*\\}")
                    System.out.println(stringCellValue);
                    cell.setCellValue("这是新填入的值");
                }
            }
        }
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        sheets.write(outputStream);

        return outputStream;



    }
}
